from flask import Flask,render_template,request,redirect,flash,session
import pymssql

connect=pymssql.connect(host='127.0.0.1',
        port='1433',
        user='sa',
        password='1560399306',
        database='student')

cur=connect.cursor()
cursor=connect.cursor()


def sql(sql):
    cursor.execute(sql)
    row = cursor.fetchall()
    connect.commit()
    return row

def ad(sql,data):
    cursor.execute(sql,data)
    connect.commit()

app = Flask(__name__)
app.secret_key='flask'

@app.route('/student')
def student():#学生前台界面
    name=session['username']
    return render_template('student_admin.html',name=name)

@app.route('/',methods=['GET','POST'])
def hello_world():#登录界面
    login=sql('select * from admin')
    if request.method=='POST':
        username=request.form.get('username')
        password=request.form.get('password')
        session['username']=username
        for temp in login:
            if username=='admin' and password=='123456':
                return redirect('fadmin')
            elif temp[0].replace(' ', '') == username and temp[1].replace(' ', '') == password:
                return redirect('/student')
            else:
                flash('用户名或密码错误')
    return render_template('login.html')

@app.route('/regiter',methods=['GET','POST'])
def regiter():#注册界面
    if request.method=='POST':
        username=request.form.get("username")
        password=request.form.get("password")
        print(username,password)
        ad("insert into admin values(%s,%s)", (username,password))
        return redirect('/')
    return render_template('register.html')
@app.route('/user')
def user():#账号管理界面
    row=sql('select * from admin')
    return render_template('user.html',row=row)
@app.route('/people')
def people():#学衡个人信息
    name=session['username']
    print(name)
    index=sql('select * from web')
    for temp in index:
        if name==temp[1]:
            print(temp)
            return render_template('people.html',temp=temp)


@app.route('/fadmin')
def fadmin():#管理员前台管理页面
    name = session['username']
    return render_template('fadmin.html',name=name)

@app.route('/frist')
def frist():
    return render_template('datetime.html')

@app.route('/admin')
def admin():#学生信息表
       row=sql("select 学号,姓名,年龄,性别,学院 from web")
       return render_template('admin.html',row=row)

@app.route('/SC')
def SC():#成绩信息表
    row1=sql("select  * from score")
    return render_template('Sc.html', row1=row1)

@app.route('/student_sc')
def student_sc():#学生查看个人成绩
    name = session['username']
    list = sql('select * from web')
    for temp in list:
        if name == temp[1]:
            row = sql('select * from score where Sno=' + str(temp[0]))
    return render_template('student_sc.html',row=row)

@app.route('/course')
def course():#课程信息
    row=sql("select * from Course")
    return render_template('course.html',row=row)

@app.route('/student_course')
def student_course():#课程信息
    row=sql("select * from Course")
    return render_template('student_course.html',row=row)
@app.route('/add',methods=["GET","POST"])
def add():#添加学生信息
        if request.method=='POST':
            sno=request.form.get('sno')
            sname=request.form.get('sname')
            ssex=request.form.get('ssex')
            sage=request.form.get('sage')
            sdept = request.form.get('sdept')
            demo=sql('select * from web')
            lst=[i[0] for i in demo]
            if sno in lst:
                flash('该学生已存在')
            else:
                ad("insert into web values(%s,%s,%s,%s,%s)",(sno,sname,ssex,sage,sdept))
                return redirect('/admin')
        return render_template('add.html')

@app.route('/add_course',methods=["GET","POST"])
def add_course():#管理员添加课程
    if request.method == 'POST':
        sno = request.form.get('cno')
        sname = request.form.get('cname')
        ssex = request.form.get('cpno')
        sage = request.form.get('credit')
        row=sql('select * from Course')
        lst=[temp[1].replace(' ','') for temp in row]
        l=[temp[0].replace(' ','')for temp in row]
        if sname in lst or sno in l:
            flash('此课程已存在')
        else:
            ad("insert into Course values(%s,%s,%s,%s)", (sno, sname, sage, ssex))
            return redirect('/course')
    return render_template('add_course.html')

@app.route('/add_Sc',methods=["GET","POST"])
def add_Sc():#管理员添加成绩
    if request.method=='POST':
        id=request.form.get('Id')
        sno=request.form.get("Sno")
        cno = request.form.get("Cno")
        grades = request.form.get("Grades")
        print(sno,cno,grades)
        ad("insert into score values(%s,%s,%s,%s)",(id,sno,cno,grades))
        return redirect('/SC')
    return render_template('add_Sc.html')
@app.route('/add_user',methods=['GET','POST'])

def add_user():#管理员添加用户
    if request.method=='POST':
        name=request.form.get('name')
        password = request.form.get('password')
        time=request.form.get('time')
        print(name,password,time)
        demo=sql('select * from admin')
        lst=[temp[0].replace(' ','') for temp in demo]
        if name in lst:
            flash('该用户已存在')
        else:
            ad("insert into admin values(%s,%s,%s)", (name, password, time))
            return redirect('/user')
    return render_template('add_user.html')
@app.route('/delete')
def delete():#管理员删除学生信息
    name = request.args.get('sname')
    print(name)
    lst = sql('select * from web')
    for demo in lst:
        if demo[1].replace(' ', '') == name:
            ad("delete web where 姓名=%s", (demo[1]))
            return redirect('/admin')

@app.route('/delete_course')
def delete_course():#管理员删除相应课程
    name = request.args.get('cname')
    print(name)
    lst = sql('select * from Course')
    for demo in lst:
        if demo[1].replace(' ', '') == name:
            ad("delete Course where Cname=%s", (demo[1]))
            return redirect('/course')

@app.route('/delete_Sc')
def delete_Sc():#管理员删除成绩
    name = request.args.get('cno')
    print(name)
    lst = sql('select * from score')
    print(lst)
    for demo in lst:
        if demo[0].replace(' ','') == name:
            ad("delete score where id=%s", (demo[0]))
    return redirect('/SC')

@app.route('/change',methods=['GET','POST'])
def change():
    username=request.args.get('name')
    if request.method=='POST':
        sno = request.form.get('sno')
        sname = request.form.get('sname')
        ssex = request.form.get('ssex')
        sage = request.form.get('sage')
        sdept = request.form.get('sdept')
        lst=sql("select * from web")
        for demo in lst:
            if demo[1]==username:
                ad("update web set 学号=%s,姓名=%s,年龄=%s,性别=%s,学院=%s where 姓名=%s",
                               (sno, sname, ssex, sage, sdept, demo[1]))
        return redirect('/admin')
    row=sql("select * from web")
    for demo in row:
        if demo[1].replace(' ','')==username:
            return render_template('change.html',demo=demo)

@app.route('/change_course',methods=['GET','POST'])
def change_course():
    canme=request.args.get('cname')
    print(canme)
    if request.method=='POST':
        cno = request.form.get('cno')
        cname = request.form.get('cname')
        cpno = request.form.get('cpno')
        credit = request.form.get('credit')
        lst=sql("select * from Course")
        for demo in lst:
            if demo[1].replace(' ','')==canme:
                ad("update Course set Cno=%s,Cname=%s,Cpno=%s,Ccredit=%s where Cname=%s",
                   (cno,cname,cpno,credit,demo[1]))
        return redirect('/course')
    row=sql("select * from Course")
    for demo in row:
        if demo[1].replace(' ', '') == canme:
            return render_template("change_course.html",demo=demo)

@app.route('/change_Sc',methods=['GET','POST'])
def change_Sc():
    index=request.args.get("cno")
    if request.method=='POST':
        id=request.form.get('Id')
        sno = request.form.get('sno')
        cno = request.form.get('cno')
        grade=request.form.get('grade')
        lst=sql('select * from score')
        for demo in lst:
            if demo[0].replace(' ','')==index:
                ad("update score set id=%s, Sno=%s,Cno=%s,Grade=%s where id=%s",
                   (id, sno, cno, grade, demo[0]))
        return redirect('/SC')
    row=sql('select * from score')
    for demo in row:
        if demo[0].replace(' ','')==index:
            return render_template('change_Sc.html',demo=demo)

if __name__ == '__main__':
    app.run(debug=True)
